# -*- coding: utf-8 -*-
##############################################################################
#
#    OpenERP, Open Source Management Solution
#    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU Affero General Public License as
#    published by the Free Software Foundation, either version 3 of the
#    License, or (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU Affero General Public License for more details.
#
#    You should have received a copy of the GNU Affero General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################
import xlwt
import math
import cStringIO
from .. import format_common

from openerp.osv import osv, fields
import time, os
import base64
from openerp.tools.translate import _
from datetime import datetime
import logging
_logger = logging.getLogger(__name__)
from xlwt import *

class param_bank_statement_reconcile_report(osv.osv_memory):
    _name = 'param.bank.statement.reconcile.report'
    _description = 'Bank Statement Reconcile Report'
    _columns = {
        'account_selection': fields.selection([('all_vall','All'),('def','Default'),('input', 'Input'),('selection','Selection')],'Account Selection', required=True),
        'account_default_from':fields.many2one('account.account', 'Account From', required=False),
        'account_default_to':fields.many2one('account.account', 'Account To', required=False),
        'account_input_from': fields.char('Account From', size=128),
        'account_input_to': fields.char('Account To', size=128),
        'account_name_ids' :fields.many2many('account.account', 'bank_account_checked_name_rel', 'account_id', 'account_name', 'Account'),
        'journal_selection': fields.selection([('all_vall','All'),('def','Default'),('input', 'Input'),('selection','Selection')],'Journal Selection', required=True),
        'journal_default_from':fields.many2one('account.journal', 'Journal From', required=False),
        'journal_default_to':fields.many2one('account.journal', 'Journal To', required=False),
        'journal_input_from': fields.char('Journal From', size=128),
        'journal_input_to': fields.char('Journal To', size=128),
        'journal_name_ids' :fields.many2many('account.journal', 'bank_account_journal_checked_name_rel', 'journal_id', 'journal_name', 'Journal'),
        'currency_selection': fields.selection([('all_vall','All'),('def','Default'),('input', 'Input'),('selection','Selection')],'Currency Selection', required=True),
        'currency_default_from':fields.many2one('res.currency', 'Currency From', required=False),
        'currency_default_to':fields.many2one('res.currency', 'Currency To', required=False),
        'currency_input_from':fields.char('Currency From', size=128),
        'currency_input_to':fields.char('Currency To', size=128),
        'currency_name_ids' :fields.many2many('res.currency', 'bank_account_currency_checked_name_rel', 'curr_id', 'curr_name', 'Currency'),
        'sortby_ids': fields.one2many('param.bank.statement.reconcile.report.sortby','sortby_id', 'Sort By'),
        'target_move': fields.selection([('posted', 'All Posted Entries'),
                                        ('all', 'All Entries'),
                                        ], 'Target Moves', required=True),      
    }
    
    _defaults = {
        'account_selection': 'all_vall',
        'journal_selection': 'all_vall',
        'currency_selection': 'all_vall',
        'target_move': 'all'
    }
    
    def onchange_account_selection(self, cr, uid, ids, account_selection, context=None):
        if context is None:
            context = {}
        res = {}
        if account_selection != 'all_vall':
             res['value'] = {'account_default_from': False, 'account_default_to': False, 'account_input_from': False, 'account_input_to': False, 'account_name_ids': False,
                                }
        return res
    
    def onchange_journal_selection(self, cr, uid, ids, journal_selection, context=None):
        if context is None:
            context = {}
        res = {}
        if journal_selection != 'all_vall':
             res['value'] = {'journal_default_from': False, 'journal_default_to': False, 'journal_input_from': False, 'journal_input_to': False, 'journal_name_ids': False,
                                }
        return res
    
    def onchange_currency_selection(self, cr, uid, ids, currency_selection, context=None):
        if context is None:
            context = {}
        res = {}
        if currency_selection != 'all_vall':
             res['value'] = {'currency_default_from': False, 'currency_default_to': False, 'currency_input_from': False, 'currency_input_to': False, 'currency_name_ids': False,
                                }
        return res
    
    def _search_data(self, cr, uid, ids, data_obj, val_data, filter_selection, default_from, default_to, input_from, input_to, selection_ids, context=None):
        
        data_header = ''
        data_ids = []

        if context is None:
            context = {}
        
        if filter_selection == 'all_vall':
            data_ids = data_obj.search(cr, uid, val_data, order='id ASC')
        if filter_selection == 'def':
            data_found = False
            if default_from and data_obj.browse(cr, uid, default_from) and data_obj.browse(cr, uid, default_from).name:
                default_from_header = data_obj.browse(cr, uid, default_from).name
                data_found = True
                val_data.append(('name', '>=', default_from_header))
            if default_to and data_obj.browse(cr, uid, default_to) and data_obj.browse(cr, uid, default_to).name:
                default_to_header = data_obj.browse(cr, uid, default_to).name
                data_found = True
                val_data.append(('name', '<=', default_to_header))
            if data_found:
                data_header = '"' + default_from_header + '" - "' + default_to_header + '"'
                data_ids = data_obj.search(cr, uid, val_data, order='id ASC')
        if filter_selection == 'input':
            data_found = False
            if input_from:
                data_ids_from = data_obj.search(cr, uid, [('name','=ilike',input_from + '%')], order="name", limit=1)
                if data_ids_from:
                    input_from_name = data_obj.browse(cr, uid, data_ids_from[0]).name
                    data_found = True
                    val_data.append(('name', '>=', input_from_name))
            if input_to:
                data_ids_to = data_obj.search(cr, uid, [('name','=ilike', input_to + '%')], order="name desc", limit=1)
                if data_ids_to:
                    input_to_name = data_obj.browse(cr, uid, data_ids_to[0]).name
                    data_found = True
                    val_data.append(('name', '<=', input_to_name))
            data_header = '"' + input_from + '" - "' + input_to + '"'
            if data_found:
                data_ids = data_obj.search(cr, uid, val_data, order='id ASC')
        if filter_selection == 'selection':
            if selection_ids:
                sel_ids = ''
                for sel in  data_obj.browse(cr, uid, selection_ids):
                    sel_ids += ('"%s",')%(sel.name,)

                data_header = '[' + sel_ids +']'
                data_ids = selection_ids
                
        return data_header, data_ids
    
    def _build_contexts(self, cr, uid, ids, data, context=None):
        if context is None:
            context = {}
        result = {}
        result['account_selection'] = data['form']['account_selection'] or False
        result['journal_selection'] = data['form']['journal_selection'] or False
        result['currency_selection'] = data['form']['currency_selection'] or False
        result['sortby_ids'] = data['form']['sortby_ids'] or False
        result['target_move'] = data['form']['target_move'] or False
        
        result['output_account_header'], result['account_ids'] = self._search_data(cr, uid, ids, self.pool.get('account.account'),
                                                                                      [],
                                                                                      data['form']['account_selection'],
                                                                                      data['form']['account_default_from'] or False,
                                                                                      data['form']['account_default_to'] or False,
                                                                                      data['form']['account_input_from'] or False,
                                                                                      data['form']['account_input_to'] or False,
                                                                                      data['form']['account_name_ids'] or False,
                                                                                      context=context)

        result['output_journal_header'], result['journal_ids'] = self._search_data(cr, uid, ids, self.pool.get('account.journal'),
                                                                                      [],
                                                                                      data['form']['journal_selection'],
                                                                                      data['form']['journal_default_from'] or False,
                                                                                      data['form']['journal_default_to'] or False,
                                                                                      data['form']['journal_input_from'] or False,
                                                                                      data['form']['journal_input_to'] or False,
                                                                                      data['form']['journal_name_ids'] or False,
                                                                                      context=context)
        
        result['output_currency_header'], result['currency_ids'] = self._search_data(cr, uid, ids, self.pool.get('res.currency'),
                                                                                      [],
                                                                                      data['form']['currency_selection'],
                                                                                      data['form']['currency_default_from'] or False,
                                                                                      data['form']['currency_default_to'] or False,
                                                                                      data['form']['currency_input_from'] or False,
                                                                                      data['form']['currency_input_to'] or False,
                                                                                      data['form']['currency_name_ids'] or False,
                                                                                      context=context)
        
        return result
    
    def create_vat(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        datas = {'ids': context.get('active_ids', [])}
        datas['model'] = 'param.bank.statement.reconcile.report'
        datas['form'] = self.read(cr, uid, ids)[0]
        return {
            'type': 'ir.actions.report.xml',
            'report_name': 'bank.statement.reconcile.report_landscape',
            'datas': datas,
            'nodestroy':True,
        }
    
    def print_report(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        data = {}
        data['ids'] = context.get('active_ids', [])
        data['model'] = context.get('active_model', 'ir.ui.menu')
        data['form'] = self.read(cr, uid, ids, ['target_move','account_selection','account_default_from','account_default_to','account_input_from','account_input_to', 'account_name_ids', \
                                                'journal_selection','journal_default_from','journal_default_to','journal_input_from','journal_input_to', 'journal_name_ids', \
                                                'currency_selection','currency_default_from','currency_default_to','currency_input_from','currency_input_to', 'currency_name_ids', \
                                                'sortby_ids'], context=context)[0]
        for field in ['target_move','account_selection','account_default_from','account_default_to','account_input_from','account_input_to', 'account_name_ids', \
                      'journal_selection','journal_default_from','journal_default_to','journal_input_from','journal_input_to', 'journal_name_ids', \
                      'currency_selection','currency_default_from','currency_default_to','currency_input_from','currency_input_to', 'currency_name_ids', \
                      'sortby_ids']:
            if isinstance(data['form'][field], tuple):
                data['form'][field] = data['form'][field][0]   
        used_context = self._build_contexts(cr, uid, ids, data, context=context)

        return self._get_tplines_xls(cr, uid, ids, used_context, context=context)
    
    def _get_tplines_xls(self, cr, uid, ids, form, context):

        if not ids:
            ids = data['ids']
        if not ids:
            return []
        
        user_obj = self.pool.get('res.users')
        aml_obj = self.pool.get('account.move.line')
        sortby_obj = self.pool.get('param.bank.statement.reconcile.report.sortby')
        dp_obj = self.pool.get('decimal.precision')
        
        result         = []
        results        = []
        val_bank_recon = []
        sortby_ids     = []
        
        target_move = form['target_move']
        journal_ids = form['journal_ids']
        account_ids = form['account_ids']
        currency_ids = form['currency_ids']
        output_journal_header = form['output_journal_header']
        output_account_header = form['output_account_header']
        output_currency_header = form['output_currency_header']
        output_sortby_header = ''
        
        dp_data = {}
        decimal_format = easyxf(num_format_str = '0.000000')
        dp_ids = dp_obj.search(cr, uid, [])

        for dp_id in dp_obj.browse(cr, uid, dp_ids, context=None) :
            dp_data[dp_id.name] = dp_id.digits
        
        dp_account = dp_data['Account']
        dp_exrate = dp_data['Payment Term']
        
        account_digits = int(math.pow(10, dp_account))
        digit_account_output = '#,##0.' + str(account_digits)[1:]
        account_format = easyxf(num_format_str = digit_account_output)
        
        exrate_digits = int(math.pow(10, dp_exrate))
        digit_exrate_output = '#,##0.' + str(exrate_digits)[1:]
        exrate_format = easyxf(num_format_str = digit_exrate_output)
        
        val_bank_recon.append(('checked_by_user','=',True))
        val_bank_recon.append(('account_id.bank_account','=',True))
        
        if journal_ids:
            val_bank_recon.append(('journal_id','in',journal_ids))
        if account_ids:
            val_bank_recon.append(('account_id','in',account_ids))
        if currency_ids:
            val_bank_recon.append(('currency_id','in',currency_ids))
        
        if 'sortby_ids' in form:
            sortby_ids = sortby_obj.search(cr, uid, [('id', 'in', form['sortby_ids'])], order='seq desc')
        
        if sortby_ids:
            for item in sortby_obj.browse(cr, uid, sortby_ids):
                result.append({
                        'seq' : item['seq'],
                        'sort_by' : item['sort_by'],
                        })
            result = result and sorted(result, key=lambda val_res: val_res['seq']) or []               
            
            for rs in result:
                sort_name = ''
                if rs['sort_by'] == 'account_name':
                    sort_name = 'Account'
                elif rs['sort_by'] == 'journal_name':
                    sort_name = 'Journal'
                elif rs['sort_by'] == 'curr_name':
                    sort_name = 'Currency'
                
                output_sortby_header += ('"%s",')%(sort_name,)
        
        target_move_str = "Target Move : All Entries"
        if target_move == 'posted': 
            target_move_str = "Target Move : All Posted Entries"
            val_bank_recon.append(('move_id.state','=','posted'))
        
        bank_reconcile_ids = aml_obj.search(cr, uid, val_bank_recon)
        if bank_reconcile_ids:
            br_ids = []
            
            for br in aml_obj.browse(cr, uid, bank_reconcile_ids):
                br_ids.append({
                    'journal_name' : br.journal_id.name,
                    'ref' : br.ref,
                    'account_name' : br.account_id.name,
                    'debit' : br.debit,
                    'credit' : br.credit,
                    'curr_name' : br.currency_id.name,
                    'amt_orig' : br.amount_original,
                    'exrate' : br.exrate,
                    'checked_by' : br.bank_check_by.name
                })
            if sortby_ids:
                for bank_recon_sortby in sortby_obj.browse(cr, uid, sortby_ids):
                    br_ids = br_ids and sorted(br_ids, key=lambda val_res: val_res[bank_recon_sortby.sort_by]) or []               
            else:
                br_ids = br_ids and sorted(br_ids, key=lambda val_res: val_res['curr_name']) or []               
                br_ids = br_ids and sorted(br_ids, key=lambda val_res: val_res['account_name']) or []
                br_ids = br_ids and sorted(br_ids, key=lambda val_res: val_res['journal_name']) or []
            
            for br_result in br_ids:
                results.append({
                    'journal_name' : br_result['journal_name'] or '',
                    'ref' : br_result['ref'] or '',
                    'account_name' : br_result['account_name'] or '',
                    'debit' : br_result['debit'] or 0.00,
                    'credit' : br_result['credit'] or 0.00,
                    'curr_name' : br_result['curr_name'] or '',
                    'amt_orig' : br_result['amt_orig'] or 0.00,
                    'exrate' : br_result['exrate'] or 0.000000,
                    'checked_by' : br_result['checked_by'] or '',
                })
                
        workbook = xlwt.Workbook()
        M_header_tstyle = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=300)
        report_tstyle = format_common.font_style(position='left', bold=1, fontos='black', font_height=190)
        report_tstyle1 = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        header_tstyle = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        header_tstyle_c = format_common.font_style(position='center', bold=1, border=7, fontos='black', font_height=180)
        header_tstyle_l = format_common.font_style(position='left', bold=1,border=7, fontos='black', font_height=180)
        header_tstyle_c_Top = format_common.font_style(position='center', bold=1, border=4, fontos='black', font_height=180)
        header_tstyle_c_Bottom = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=180)
        header_tstyle_r = format_common.font_style(position='right', bold=1,  border=7,fontos='black', font_height=180)
        view_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        view_tstyle_c = format_common.font_style(position='center', fontos='black', font_height=180)
        view_tstyle_l = format_common.font_style(position='left', fontos='black', font_height=180)
        view_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        view_tstyle_r_b = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        other_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        other_tstyle_c = format_common.font_style(position='center', fontos='black', font_height=180)
        other_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        text_tstyle = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        text_tstyle1 = format_common.font_style(position='left', bold=1, fontos='black', font_height=220)
        text_tstyle2 = format_common.font_style(position='left', fontos='black', font_height=220)
        text_tstyle_c = format_common.font_style(position='center', bold=1, fontos='black', font_height=180)
        text_tstyle_l = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        text_tstyle_r = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        text_tstyle_r_closing = format_common.font_style(position='right', bold=1,border=7, fontos='black', font_height=180)
        text_tstyle_Top = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Bottom = format_common.font_style(position='left', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_Top_r = format_common.font_style(position='right', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Top_l = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Top_r_r = format_common.font_style(position='right', bold=1, border=6, fontos='black', font_height=180)
        text_tstyle_Top_l_l = format_common.font_style(position='left', bold=1, border=6, fontos='black', font_height=180)
        text_tstyle_Bottom_r = format_common.font_style(position='right', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_TopBottom = format_common.font_style(position='left', bold=1, border=2, fontos='black', font_height=180)
        text_tstyle_TopBottom_r = format_common.font_style(position='right', bold=1, border=2, fontos='black', font_height=180)
        
        sheet = workbook.add_sheet('Bank Recon Report XLS')
        sheet.row(0).height = 350
        row=0
        
        company_name = user_obj.browse(cr, uid, uid).company_id.name
        sheet.write_merge(row, row, 0, 8, company_name, report_tstyle1)
        
        row+=1
        sheet.write_merge(row, row, 0, 8, 'Bank Reconciliation Report', report_tstyle1)
        
        user_ids = user_obj.search(cr, uid, [('id','=',uid)])
        if user_ids :
            tz = user_obj.browse(cr, uid, user_ids[0])
            tz_name = tz.tz
            user_name = tz.name
            
        os.environ['TZ'] = str(tz_name)
        time.tzset()
        
        row+=1
        sheet.write_merge(row, row, 0, 2, 'Printed date : ' + time.strftime("%Y/%m/%d") + '  Time : '+ time.strftime("%H:%M:%S"), view_tstyle)
        sheet.write_merge(row, row, 3, 5, 'Printed by : ' + user_name, view_tstyle)
         
        row+=1
        sheet.write(row, 0, '', view_tstyle)
        
        if form['journal_selection'] != 'all_vall' :
            row += 1
            sheet.write_merge(row, row, 0, 8, '   Journal : ' + output_journal_header, text_tstyle)
        
        if form['account_selection'] != 'all_vall':
            row += 1
            sheet.write_merge(row, row, 0, 8, '   Account : ' + output_account_header, text_tstyle)
        
        if form['currency_selection'] != 'all_vall':
            row += 1
            sheet.write_merge(row, row, 0, 8, '   Currency : ' + output_currency_header, text_tstyle)
              
        if form['sortby_ids']:
            row += 1
            sheet.write_merge(row, row, 0, 8, '   Sort By : ' + output_sortby_header, text_tstyle)
        
        row += 1  
        sheet.write_merge(row, row, 0, 8,"   "+target_move_str, text_tstyle)
        
        row+=1
        sheet.write(row, 0, '', view_tstyle)
        
        row+=1
        sheet.write(row, 0, 'Journal', header_tstyle_c)
        sheet.write(row, 1, 'Reference', header_tstyle_c)
        sheet.write(row, 2, 'Account', header_tstyle_c)
        sheet.write(row, 3, 'Debit', header_tstyle_c)
        sheet.write(row, 4, 'Credit', header_tstyle_c)
        sheet.write(row, 5, 'Currency', header_tstyle_c)
        sheet.write(row, 6, 'Amount Orig.', header_tstyle_c)
        sheet.write(row, 7, 'ExRate', header_tstyle_c)
        sheet.write(row, 8, 'Checked By', header_tstyle_c)
         
        sheet.col(0).width = 256 * 25
        sheet.col(1).width = 256 * 18
        sheet.col(2).width = 256 * 28
        sheet.col(3).width = 256 * 10
        sheet.col(4).width = 256 * 10
        sheet.col(5).width = 256 * 10
        sheet.col(6).width = 256 * 15
        sheet.col(7).width = 256 * 12
        sheet.col(8).width = 256 * 15
        
        for rs1 in results:
            row += 1
            sheet.write(row, 0, str(rs1['journal_name']), view_tstyle_l)
            sheet.write(row, 1, str(rs1['ref']), view_tstyle_l)
            sheet.write(row, 2, str(rs1['account_name']), view_tstyle_l)
            sheet.write(row, 3, rs1['debit'], account_format)    
            sheet.write(row, 4, rs1['credit'], account_format)
            sheet.write(row, 5, str(rs1['curr_name']), view_tstyle_l)
            sheet.write(row, 6, rs1['amt_orig'], account_format)
            sheet.write(row, 7, rs1['exrate'], exrate_format)
            sheet.write(row, 8, str(rs1['checked_by']), view_tstyle_l)
        
        row += 1
        sheet.write_merge(row, row, 0, 8, 'End of Report', text_tstyle_Top_l) 
        
        stream = cStringIO.StringIO()
        workbook.save(stream)
            
        cr.execute(""" DELETE FROM output""")
        attach_id = self.pool.get('output').create(cr, uid, {'name':'Bank Statement Reconciliation Report.xls', 'xls_output':base64.encodestring(stream.getvalue())})
        return {
                'name': _('Notification'),
                'context': context,
                'view_type': 'form',
                'view_mode': 'form',
                'res_model': 'output',
                'res_id':attach_id,
                'type': 'ir.actions.act_window',
                'target':'new'
                }
    
class param_bank_statement_reconcile_report_sortby(osv.osv_memory):
    
    _name = "param.bank.statement.reconcile.report.sortby"
    
    _columns = {
        'sortby_id': fields.many2one('param.bank.statement.reconcile.report', 'Sort By', required=True, ondelete='cascade'),
        'seq': fields.integer('Sequence', required=True),
        'sort_by': fields.selection([('journal_name', 'Journal'), ('account_name','Account'), ('curr_name', 'Currency')],'Sort by', required=True),
    }        